package com.spar.openprojects.server;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.PrintStream;
import java.net.Socket;
import java.net.UnknownHostException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.google.gwt.user.server.rpc.RemoteServiceServlet;
import com.spar.openprojects.client.RemoteDataService;
import com.spar.openprojects.server.datasource.DatasourceInformixETime;
import com.spar.openprojects.server.datasource.DatasourceMySqlOpenprojects;
import com.spar.openprojects.shared.BusinessPartner;
import com.spar.openprojects.shared.ETimeProject;
import com.spar.openprojects.shared.Member;
import com.spar.openprojects.shared.Order;
import com.spar.openprojects.shared.OrderItem;
import com.spar.openprojects.shared.Project;
import com.spar.openprojects.shared.rowmapper.BusinessPartnerRowMapper;
import com.spar.openprojects.shared.rowmapper.ETimeProjectRowMapper;
import com.spar.openprojects.shared.rowmapper.OrderItemRowMapper;
import com.spar.openprojects.shared.rowmapper.OrderRowMapper;
import com.spar.openprojects.shared.rowmapper.ProjectRowMapper;
import com.spar.openprojects.shared.utilities.LogManager;
import com.sql.database.Database;
import com.sql.database.IConnectionFactory;
import com.sql.database.IRowMapper;

@SuppressWarnings("serial")
public class RemoteDataServiceProvider extends RemoteServiceServlet implements
		RemoteDataService {

	@SuppressWarnings("unchecked")
	@Override
	public List<Project> getProjects(Boolean active, Member user) {
		IConnectionFactory connectionFactory = new DatasourceMySqlOpenprojects();
		LogManager logManager = new LogManager(connectionFactory);
		Database database = new Database(connectionFactory);
		IRowMapper rowMapper = new ProjectRowMapper();
		String sqlStatement = "select p.* ,m.*, sorder.sumOrder  from project as p inner join member as m on m.id = p.member_id left join (select sum(i.amount) sumOffer, o.project_id from openprojects.order o inner join orderitem i on o.id = i.order_id and orderitem_type_id = 1 group by o.project_id) as soffer on p.id = soffer.project_id left join (select sum(i.amount) sumOrder, o.project_id from openprojects.order o inner join orderitem i on o.id = i.order_id and orderitem_type_id = 2 group by o.project_id) as sorder on p.id = sorder.project_id left join (select sum(i.amount) sumBill, o.project_id from openprojects.order o inner join orderitem i on o.id = i.order_id and orderitem_type_id = 3 group by o.project_id) as sbill on p.id = sbill.project_id where p.active = "
				+ (active ? 1 : 0);
		List<Project> projects = new ArrayList<Project>();
		try {
			projects = database.ExecuteQuery(sqlStatement, rowMapper);
		} catch (Exception e) {
			logManager.Log("Error", "get projects", e.getMessage());
		}

		// etime data
		String projectIds = "";
		for (Project project : projects) {
			if (project.getNumber() != null && project.getNumber() != 0) {
				projectIds += project.getNumber() + ",";
			}
		}
		if (projectIds.length() > 0)
			projectIds = projectIds.substring(0, projectIds.length() - 1);
		sqlStatement = "select c.cat_id project_id, round(sum(t.duration)/8,2) time from time t inner join subcategory s on t.sc_id = s.id inner join category c on c.cat_id = s.cat_id where c.cat_id in ("
				+ projectIds
				+ ") and s.cat_id in ("
				+ projectIds
				+ ") and (t.abt_kbez like 'ICS%' or t.abt_kbez like 'HZ4%') and t.valid = 1 group by c.cat_id order by c.cat_id desc";
		connectionFactory = new DatasourceInformixETime();
		database = new Database(connectionFactory);
		rowMapper = new ETimeProjectRowMapper();
		List<ETimeProject> eTimeProjects = new ArrayList<ETimeProject>();
		try {
			eTimeProjects = database.ExecuteQuery(sqlStatement, rowMapper);
		} catch (Exception e) {
			logManager.Log("Error", "get projects - etime", e.getMessage());
		}
		for (ETimeProject etp : eTimeProjects) {
			for (Project p : projects) {
				if (etp.getNumber().equals(p.getNumber())) {
					p.setInternalDaysUsed(etp.getInternalDays());
					break;
				}

			}
		}
		return projects;
	}

	@Override
	public Order persistOrder(Order order, Member user) throws Exception {
		IConnectionFactory connectionFactory = new DatasourceMySqlOpenprojects();
		LogManager logManager = new LogManager(connectionFactory);
		String sqlStatement = "";
		Boolean update = false;
		if (order.getId() == null || order.getId() == 0) {
			// insert
			sqlStatement = "INSERT INTO openprojects.order "
					+ "(project_id, business_partner_id, every_year, "
					+ "deleted, member_number_updated, updated, "
					+ "member_number_created, created) VALUES "
					+ "(?,?,?,?,?, ?,?,now())";
		} else {
			update = true;
			sqlStatement = "UPDATE openprojects.order SET "
					+ "project_id = ?, " + "business_partner_id = ?, "
					+ "every_year = ?, " + "deleted = ?, "
					+ "member_number_updated = ?, " + "updated = ?, "
					+ "member_number_created = ? " + " WHERE id = "
					+ order.getId();
		}

		Connection con = null;
		PreparedStatement orderInsertUpdate = null;
		int idOrRows = 0;
		try {
			// SimpleDateFormat sdf = new
			// java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			con = connectionFactory.createConnection();
			orderInsertUpdate = con.prepareStatement(sqlStatement);
			orderInsertUpdate = setOrderPreparedStatement(orderInsertUpdate,
					order, user);

			idOrRows = orderInsertUpdate.executeUpdate();
			// get autoincrement after insert
			ResultSet rs = orderInsertUpdate.getGeneratedKeys();
			if (rs != null && rs.next()) {
				idOrRows = rs.getInt(1);
			}
			if (!update) {
				order.setId(idOrRows);
			}
			// // order history
			// sqlStatement = "INSERT INTO history_order "
			// + "(project_id, offer_amount, offer_number, offer_date, "
			// + "offer_link, order_amount, order_number, "
			// + "order_date, order_link, bill_amount, "
			// + "bill_number, bill_date, bill_link, "
			// +
			// "date_approval, business_partner_id, deleted, member_number_updated, updated, member_number_created, created, order_id) VALUES "
			// + "(?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,now(), ?)";
			// orderInsertUpdate = con.prepareStatement(sqlStatement);
			// orderInsertUpdate = setOrderPreparedStatement(orderInsertUpdate,
			// order);
			// orderInsertUpdate.setInt(20, order.getId());
			// orderInsertUpdate.executeUpdate();

		} catch (Exception e) {
			logManager.Log("Error", "persist order", e.getMessage());
			throw e;
		} finally {
			if (orderInsertUpdate != null)
				orderInsertUpdate.close();
			if (con != null)
				con.close();
		}
		order.getOffer().setOrderId(order.getId());
		order.getOffer().setType(1);
		order.getOffer().setCreateMember(order.getCreateMember());
		order.getOffer().setUpdateMember(order.getUpdateMember());

		order.getOrder().setOrderId(order.getId());
		order.getOrder().setType(2);
		order.getOrder().setCreateMember(order.getCreateMember());
		order.getOrder().setUpdateMember(order.getUpdateMember());

		order.setOffer(persistOrderItem(order.getOffer(), user));
		order.setOrder(persistOrderItem(order.getOrder(), user));

		List<OrderItem> bills = new ArrayList<OrderItem>();
		for (OrderItem oi : order.getBills()) {
			oi.setOrderId(order.getId());
			oi.setType(3);
			oi.setCreateMember(order.getCreateMember());
			oi.setUpdateMember(order.getUpdateMember());
			bills.add(persistOrderItem(oi, user));
		}
		order.setBills(bills);
		return order;

	}

	private PreparedStatement setOrderPreparedStatement(
			PreparedStatement preparedStatement, Order order, Member user)
			throws SQLException {
		preparedStatement.setInt(1, order.getProjectId());
		preparedStatement.setInt(2, order.getBusinessPartner().getId());
		// every_year
		if (order.getEveryYear())
			preparedStatement.setInt(3, 1);
		else
			preparedStatement.setInt(3, 0);
		// deleted
		preparedStatement.setInt(4, 0);
		// member_number_updated
		preparedStatement.setString(5, user.getNumber());
		// updated
		preparedStatement.setTimestamp(6,
				new java.sql.Timestamp((new Date()).getTime()));
		// member_number_created
		preparedStatement.setString(7, order.getCreateMember().getNumber());
		return preparedStatement;
	}

	public OrderItem persistOrderItem(OrderItem orderItem, Member user)
			throws Exception {
		IConnectionFactory connectionFactory = new DatasourceMySqlOpenprojects();
		LogManager logManager = new LogManager(connectionFactory);
		String sqlStatement = "";
		Boolean update = false;
		if (orderItem.getId() == null || orderItem.getId() == 0) {
			// insert
			sqlStatement = "INSERT INTO orderitem "
					+ "(order_id,orderitem_type_id,amount,number,date,"
					+ "link,date_approval,deleted,member_number_updated,updated,"
					+ "member_number_created,created) VALUES "
					+ "(?,?,?,?,?, ?,?,?,?,?, ?,now())";
		} else {
			update = true;
			sqlStatement = "UPDATE orderitem SET "
					+ "order_id = ?, orderitem_type_id = ?, "
					+ "amount = ?, number = ?, " + "date = ?, link = ?, "
					+ "date_approval = ?, deleted = ?, "
					+ "member_number_updated = ?, updated = ?, "
					+ "member_number_created = ? WHERE id = "
					+ orderItem.getId();
		}

		Connection con = null;
		PreparedStatement orderItemInsertUpdate = null;
		Integer idOrRows = 0;
		try {
			// SimpleDateFormat sdf = new
			// java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			con = connectionFactory.createConnection();
			orderItemInsertUpdate = con.prepareStatement(sqlStatement);
			orderItemInsertUpdate = setOrderItemPreparedStatement(
					orderItemInsertUpdate, orderItem, user);

			idOrRows = orderItemInsertUpdate.executeUpdate();
			// get autoincrement after insert
			ResultSet rs = orderItemInsertUpdate.getGeneratedKeys();
			if (rs != null && rs.next()) {
				idOrRows = rs.getInt(1);
			}
			if (!update) {
				orderItem.setId(idOrRows);
			}

		} catch (Exception e) {
			logManager.Log("Error", "persist orderitem", e.getMessage());
			throw e;
		} finally {
			if (orderItemInsertUpdate != null)
				orderItemInsertUpdate.close();
			if (con != null)
				con.close();
		}
		return orderItem;

	}

	private PreparedStatement setOrderItemPreparedStatement(
			PreparedStatement preparedStatement, OrderItem orderItem,
			Member user) throws SQLException {
		preparedStatement.setInt(1, orderItem.getOrderId());
		preparedStatement.setInt(2, orderItem.getType());
		preparedStatement.setDouble(3, orderItem.getAmount());
		preparedStatement.setString(4, orderItem.getNumber());
		if (orderItem.getDate() == null) {
			preparedStatement.setTimestamp(5, null);
		} else {
			preparedStatement.setTimestamp(5, new java.sql.Timestamp(orderItem
					.getDate().getTime()));
		}
		preparedStatement.setString(6, orderItem.getLink());
		if (orderItem.getReleaseDate() == null) {
			preparedStatement.setTimestamp(7, null);
		} else {
			preparedStatement.setTimestamp(7, new java.sql.Timestamp(orderItem
					.getReleaseDate().getTime()));
		}
		// deleted
		preparedStatement.setInt(8, orderItem.getDeleted());
		// member_number_updated
		preparedStatement.setString(9, user.getNumber());
		// updated
		preparedStatement.setTimestamp(10,
				new java.sql.Timestamp((new Date()).getTime()));
		// member_number_created
		preparedStatement
				.setString(11, orderItem.getCreateMember().getNumber());
		return preparedStatement;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<BusinessPartner> getBusinessPartners(Member user) {
		IConnectionFactory connectionFactory = new DatasourceMySqlOpenprojects();
		LogManager logManager = new LogManager(connectionFactory);
		Database database = new Database(connectionFactory);
		IRowMapper rowMapper = new BusinessPartnerRowMapper();
		String sqlStatement = "select b.* from business_partner b";
		List<BusinessPartner> businessPartners = new ArrayList<BusinessPartner>();
		try {
			businessPartners = database.ExecuteQuery(sqlStatement, rowMapper);
		} catch (Exception e) {
			logManager.Log("Error", "get business partners", e.getMessage());
		}
		return businessPartners;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Order> getOrders(Project project, Member user) {
		IConnectionFactory connectionFactory = new DatasourceMySqlOpenprojects();
		LogManager logManager = new LogManager(connectionFactory);
		Database database = new Database(connectionFactory);
		IRowMapper rowMapper = new OrderRowMapper();
		String sqlStatement = "select o.*, b.* "
				+ "from openprojects.order as o "
				+ "inner join business_partner b "
				+ "where o.business_partner_id = b.id and o.deleted = 0 and o.project_id = "
				+ project.getId() + " order by o.created desc";
		List<Order> orders = new ArrayList<Order>();
		try {
			orders = database.ExecuteQuery(sqlStatement, rowMapper);
		} catch (Exception e) {
			logManager.Log("Error", "get orders - load order", e.getMessage());
		}
		sqlStatement = "select i.* from orderitem i inner join openprojects.order o where o.id = i.order_id and o.deleted = 0 and i.deleted = 0 and o.project_id = "
				+ project.getId() + " order by o.created desc";
		rowMapper = new OrderItemRowMapper();
		List<OrderItem> orderItems = new ArrayList<OrderItem>();
		try {
			orderItems = database.ExecuteQuery(sqlStatement, rowMapper);
		} catch (Exception e) {
			logManager.Log("Error", "get orders - load orderitems",
					e.getMessage());
		}
		for (Order o : orders) {
			List<OrderItem> bills = new ArrayList<OrderItem>();
			for (OrderItem i : orderItems) {
				if (o.getId().equals(i.getOrderId())) {
					switch (i.getType()) {
					case 1:
						o.setOffer(i);
						break;
					case 2:
						o.setOrder(i);
						break;
					case 3:
						bills.add(i);
						break;
					}
				}
			}
			o.setBills(bills);
		}
		return orders;
	}

	@Override
	public String readUser() {
		IConnectionFactory connectionFactory = new DatasourceMySqlOpenprojects();
		LogManager logManager = new LogManager(connectionFactory);
		String user = "";
		Socket server = null;
		PrintStream out = null;
		String ip = "";
		ip = getThreadLocalRequest().getHeader("X-FORWARDED-FOR");

		if (ip == null) {
			ip = getThreadLocalRequest().getRemoteAddr();
		}

		try {
			server = new Socket(ip, 113);
			out = new PrintStream(server.getOutputStream());
			out.println("1111,80");
			out.flush();
			// DataInputStream result = new DataInputStream(new
			// BufferedInputStream(s_in));
			BufferedReader result = new BufferedReader(new InputStreamReader(
					server.getInputStream()));
			user = result.readLine();
			user = user.substring(user.length() - 7);
			user = user.toLowerCase();
			logManager.Log("Info", "readUser", user);
			server.close();
		} catch (UnknownHostException e) {
			System.out.println("Unknown host: " + ip);
			logManager.Log("Error", "authentication", e.getMessage());
		} catch (IOException e) {
			System.out.println("No I/O");
			logManager.Log("Error", "authentication", e.getMessage());
		}
		return user;
	}

	@Override
	public void deleteOrderItem(OrderItem orderItem, Member user)
			throws Exception {
		orderItem.setDeleted(1);
		persistOrderItem(orderItem, user);
	}
}
